
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Gman.Entity.Entities;
using Gman.Entity.Interfaces;
using Gman.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;


namespace Gman.Data
{
  //-------------------------------------------------------------------
  //Archivo     : MaterialDA.cs
  //Proyecto    : Gman
  //Creacion    : BIS(NombreDesarrollador) 20130226
  //Modificacion: 
  //Descripcion : Clase para capa de datos.
  //-------------------------------------------------------------------
  public class MaterialDA : IMaterial
  {

  Database db = DatabaseFactory.CreateDatabase();

  //-----------------------------------------------------------------------------------------------
  //Nombre: Listar
  //Objetivo: Retorna una lista de registros de tipo MaterialBE  
  //Creacion    : TISI(NombreDesarrollador) 20130226  
  //-----------------------------------------------------------------------------------------------
  public IList<MaterialBE> Listar(MaterialBE oItem)
  {
    IList<MaterialBE> lista = new List<MaterialBE>();			
    try
    {				
      DbCommand cmd = db.GetStoredProcCommand("SP_ListarMaterial");

      using (IDataReader oR = db.ExecuteReader(cmd))
      {
        lista = MakeUniqueDatos(oR);
      }
      
    }catch (Exception ex){
      throw new Exception("MaterialDA.Listar()", ex);
    }
    finally
    {
    }
    return lista;
  }

  //-----------------------------------------------------------------------------------------------
  //Nombre: Obtener
  //Objetivo: Retorna un objeto de Tipo MaterialBE  
  //Creacion    : BIS(NombreDesarrollador) 20130226  
  //-----------------------------------------------------------------------------------------------
  public MaterialBE Obtener(MaterialBE oItem)
  {
    MaterialBE itemResult = null;
    try
    {
      DbCommand cmd = db.GetStoredProcCommand("SP_ObtenerMaterial");
      db.AddInParameter(cmd, "@IDMaterial", DbType.Int64, oItem.IDMaterial);

      using (IDataReader oR = db.ExecuteReader(cmd))
      {
        while (oR.Read())
        {
          itemResult = MakeDatosMapeados(oR);
        }
      }
      return itemResult;
    }
    catch (Exception ex)
    {
      throw new Exception("MaterialDA.Obtener()", ex);                  
    }
    finally
    {
    }
  }

  //-----------------------------------------------------------------------------------------------
  //Nombre: Registrar
  //Objetivo: Almacena el registro de un objeto de tipo MaterialBE  
  //Creacion    : TISI(NombreDesarrollador) 20130226  
  //----------------------------------------------------------------------------------------------
  public int Registrar(MaterialBE oItem)
  { 
    int resultado = 0;
    try
    {
      DbCommand cmd = db.GetStoredProcCommand("SP_RegistrarMaterial");						
      //db.AddInParameter(cmd, "@IDMaterial", DbType.Int64 , oItem.IDMaterial);
      db.AddInParameter(cmd, "@CodMate", DbType.String, oItem.CodMate);
      db.AddInParameter(cmd, "@TipoMate", DbType.String, oItem.TipoMate);
      db.AddInParameter(cmd, "@FamiMate", DbType.String, oItem.FamiMate);
      db.AddInParameter(cmd, "@SubFamiMate", DbType.String, oItem.SubFamiMate);
      db.AddInParameter(cmd, "@CatMate", DbType.String, oItem.CatMate);
      db.AddInParameter(cmd, "@SubCatMate", DbType.String, oItem.SubCatMate);
      db.AddInParameter(cmd, "@DescMate", DbType.String, oItem.DescMate);
      db.AddInParameter(cmd, "@UniMedMate", DbType.String, oItem.UniMedMate);
      db.AddInParameter(cmd, "@CostoPromMate", DbType.Double, oItem.CostoPromMate);
      db.AddInParameter(cmd, "@PrecPromMate", DbType.Double, oItem.PrecPromMate);
      db.AddInParameter(cmd, "@SaldActMate", DbType.Double, oItem.SaldActMate);
      db.AddInParameter(cmd, "@StckMinMate", DbType.Double, oItem.StckMinMate);
      db.AddInParameter(cmd, "@StckRepoMate", DbType.Double, oItem.StckRepoMate);
      db.AddInParameter(cmd, "@EstadoMate", DbType.Int32 , oItem.EstadoMate);
      db.AddInParameter(cmd, "@UsuReg", DbType.String, oItem.UsuReg);
      //db.AddInParameter(cmd, "@FecReg", DbType.DateTime, oItem.FecReg);
      db.AddInParameter(cmd, "@TermCnxReg", DbType.String, oItem.TermCnxReg);
      //db.AddInParameter(cmd, "@UsuMod", DbType.String, oItem.UsuMod);
      //db.AddInParameter(cmd, "@FecMod", DbType.DateTime, oItem.FecMod);
      //db.AddInParameter(cmd, "@TermCnxMod", DbType.String, oItem.TermCnxMod);
      db.AddInParameter(cmd, "@IDEmp", DbType.Int32 , oItem.IDEmp);
      db.AddInParameter(cmd, "@IDSede", DbType.Int32, oItem.IDSede);
      
      resultado = db.ExecuteNonQuery(cmd);      

    }
    catch (Exception ex)
    {
      throw new Exception("MaterialDA.Registrar()", ex);
    }
    finally
    {

    }
    return resultado;
  }

  //-----------------------------------------------------------------------------------------------
  //Nombre: Actualizar
  //Objetivo: Actualiza el registro de un objeto de tipo MaterialBE 
  //Creacion    : TISI(NombreDesarrollador) 20130226  
  //-----------------------------------------------------------------------------------------------
  public int Actualizar(MaterialBE oItem)
  {
    int resultado = 0;
    try
    {
      DbCommand cmd = db.GetStoredProcCommand("SP_ActualizarMaterial");						
      db.AddInParameter(cmd, "@IDMaterial", DbType.Int64 , oItem.IDMaterial);
      db.AddInParameter(cmd, "@CodMate", DbType.String, oItem.CodMate);
      db.AddInParameter(cmd, "@TipoMate", DbType.String, oItem.TipoMate);
      db.AddInParameter(cmd, "@FamiMate", DbType.String, oItem.FamiMate);
      db.AddInParameter(cmd, "@SubFamiMate", DbType.String, oItem.SubFamiMate);
      db.AddInParameter(cmd, "@CatMate", DbType.String, oItem.CatMate);
      db.AddInParameter(cmd, "@SubCatMate", DbType.String, oItem.SubCatMate);
      db.AddInParameter(cmd, "@DescMate", DbType.String, oItem.DescMate);
      db.AddInParameter(cmd, "@UniMedMate", DbType.String, oItem.UniMedMate);
      db.AddInParameter(cmd, "@CostoPromMate", DbType.Decimal, oItem.CostoPromMate);
      db.AddInParameter(cmd, "@PrecPromMate", DbType.Decimal, oItem.PrecPromMate);
      db.AddInParameter(cmd, "@SaldActMate", DbType.Double, oItem.SaldActMate);
      db.AddInParameter(cmd, "@StckMinMate", DbType.Double, oItem.StckMinMate);
      db.AddInParameter(cmd, "@StckRepoMate", DbType.Double, oItem.StckRepoMate);
      db.AddInParameter(cmd, "@EstadoMate", DbType.Int32 , oItem.EstadoMate);
      //db.AddInParameter(cmd, "@UsuReg", DbType.String, oItem.UsuReg);
      //db.AddInParameter(cmd, "@FecReg", DbType.DateTime, oItem.FecReg);
      //db.AddInParameter(cmd, "@TermCnxReg", DbType.String, oItem.TermCnxReg);
      db.AddInParameter(cmd, "@UsuMod", DbType.String, oItem.UsuMod);
      //db.AddInParameter(cmd, "@FecMod", DbType.DateTime, oItem.FecMod);
      db.AddInParameter(cmd, "@TermCnxMod", DbType.String, oItem.TermCnxMod);
      db.AddInParameter(cmd, "@IDEmp", DbType.Int32 , oItem.IDEmp);
      db.AddInParameter(cmd, "@IDSede", DbType.Int32, oItem.IDSede);
      
      resultado = db.ExecuteNonQuery(cmd);

    }
    catch (Exception ex)
    {
      throw new Exception("MaterialDA.Actualizar()", ex);
    }
    finally
    {

    }
    return resultado;
  }

  //-----------------------------------------------------------------------------------------------
  //Nombre: Eliminar
  //Objetivo: Elimina el registro de un objeto de tipo MaterialBE  
  //Creacion    : BIS(NombreDesarrollador) 20130226  
  //-----------------------------------------------------------------------------------------------
  public int Eliminar(MaterialBE oItem)
  {
    int resultado = 0;
    try
    {
      DbCommand cmd = db.GetStoredProcCommand("SP_EliminarMaterial");						
      db.AddInParameter(cmd, "@IDMaterial", DbType.Int64 , oItem.IDMaterial);
      

      resultado = db.ExecuteNonQuery(cmd);      

    }
    catch (Exception ex)
    {
      throw new Exception("MaterialDA.Eliminar()", ex);
  }
  finally
  {

  }
  return resultado;
  }

  //-----------------------------------------------------------------------------------------------
  //Nombre: ListarStockMaterial
  //Objetivo: Retorna una lista de registros de tipo MaterialBE  
  //Creacion    : TISI(NombreDesarrollador) 20130226  
  //-----------------------------------------------------------------------------------------------
  public IList<MaterialBE> ListarStockMaterial(MaterialBE oItem)
  {
      IList<MaterialBE> lista = new List<MaterialBE>();
      try
      {
          #region Creando consulta
          string query = "SELECT IDMaterial,CodMate,TipoMate,FamiMate,SubFamiMate,CatMate,SubCatMate,DescMate,UniMedMate,CostoPromMate," +
                           "PrecPromMate,SaldActMate,StckMinMate,StckRepoMate FROM Material WHERE ";

          if (oItem.IDEmp != null || oItem.IDEmp > 0)   query += "IDEmp = " + oItem.IDEmp;
          if (oItem.IDSede != null && oItem.IDSede > 0)   query += " AND  IDSede = " + oItem.IDSede;
          if (oItem.FamiMate != null && !oItem.FamiMate.Equals(""))  query += " AND FamiMate = " + oItem.FamiMate;
          if (oItem.SubFamiMate != null && !oItem.SubFamiMate.Equals(""))   query += " AND SubFamiMate = " + oItem.SubFamiMate;
          if (oItem.CatMate != null && !oItem.CatMate.Equals(""))   query += " AND CatMate = " + oItem.CatMate;
          if (oItem.SubCatMate != null && !oItem.SubCatMate.Equals("")) query += " AND SubCatMate = " + oItem.SubCatMate;
          if (oItem.DescMate != null && !oItem.DescMate.Equals(""))  query += " AND DescMate LIKE '%" + oItem.DescMate + "%'";

          #endregion

          DbCommand cmd = db.GetStoredProcCommand("SP_ListarStockMaterial");
          db.AddInParameter(cmd, "@query", DbType.String, query);

          using (IDataReader oR = db.ExecuteReader(cmd))
          {
              lista = MakeUniqueDatosStock(oR);
          }

      }
      catch (Exception ex)
      {
          throw new Exception("MaterialDA.Listar()", ex);
      }
      finally
      {
      }
      return lista;
  }


  private IList<MaterialBE> MakeUniqueDatos(IDataReader oR)
  {
    IList<MaterialBE> list = new List<MaterialBE>();

    while (oR.Read())
      list.Add(MakeDatosMapeados(oR));

    return list;
  }

  private MaterialBE MakeDatosMapeados(IDataReader oR)
  {
    var itemResult = new MaterialBE();
    
      itemResult.IDMaterial= oR["IDMaterial"].ToInt64();
      itemResult.CodMate= oR["CodMate"].ToString();
      itemResult.TipoMate= oR["TipoMate"].ToString();
      itemResult.FamiMate= oR["FamiMate"].ToString();
      itemResult.SubFamiMate= oR["SubFamiMate"].ToString();
      itemResult.CatMate= oR["CatMate"].ToString();
      itemResult.SubCatMate= oR["SubCatMate"].ToString();
      itemResult.DescMate= oR["DescMate"].ToString();
      itemResult.UniMedMate= oR["UniMedMate"].ToString();
      itemResult.CostoPromMate= oR["CostoPromMate"].ToDecimal();
      itemResult.PrecPromMate= oR["PrecPromMate"].ToDecimal();
      itemResult.SaldActMate= oR["SaldActMate"].ToDecimal();
      itemResult.StckMinMate= oR["StckMinMate"].ToDecimal();
      itemResult.StckRepoMate= oR["StckRepoMate"].ToDecimal();
      itemResult.EstadoMate= oR["EstadoMate"].ToInt32();
      itemResult.UsuReg= oR["UsuReg"].ToString();
      itemResult.FecReg= oR["FecReg"].ToDateTime();
      itemResult.TermCnxReg= oR["TermCnxReg"].ToString();
      itemResult.UsuMod= oR["UsuMod"].ToString();
      itemResult.FecMod= oR["FecMod"].ToDateTime();
      itemResult.TermCnxMod= oR["TermCnxMod"].ToString();
      itemResult.IDEmp= oR["IDEmp"].ToInt32();
      itemResult.IDSede = oR["IDSede"].ToInt64();
  
    return itemResult;
  }


  private IList<MaterialBE> MakeUniqueDatosStock(IDataReader oR)
  {
      IList<MaterialBE> list = new List<MaterialBE>();

      while (oR.Read())
          list.Add(MakeDatosMapeadosStock(oR));

      return list;
  }

  private MaterialBE MakeDatosMapeadosStock(IDataReader oR)
  {
      var itemResult = new MaterialBE();

      itemResult.IDMaterial = oR["IDMaterial"].ToInt64();
      itemResult.CodMate = oR["CodMate"].ToString();
      itemResult.TipoMate = oR["TipoMate"].ToString();
      itemResult.FamiMate = oR["FamiMate"].ToString();
      itemResult.SubFamiMate = oR["SubFamiMate"].ToString();
      itemResult.CatMate = oR["CatMate"].ToString();
      itemResult.SubCatMate = oR["SubCatMate"].ToString();
      itemResult.DescMate = oR["DescMate"].ToString();
      itemResult.UniMedMate = oR["UniMedMate"].ToString();
      itemResult.CostoPromMate = oR["CostoPromMate"].ToDecimal();
      itemResult.PrecPromMate = oR["PrecPromMate"].ToDecimal();
      itemResult.SaldActMate = oR["SaldActMate"].ToDecimal();
      itemResult.StckMinMate = oR["StckMinMate"].ToDecimal();
      itemResult.StckRepoMate = oR["StckRepoMate"].ToDecimal();

      return itemResult;
  }

  #region Proveedores Por Material - Jamie

  public IList<ProveedorBE> ListarProveedoresXMaterial(MaterialBE oItem)
  {
      IList<ProveedorBE> lista = new List<ProveedorBE>();
      try
      {
          DbCommand cmd = db.GetStoredProcCommand("SP_ListarProveedoresXMaterial");
          db.AddInParameter(cmd, "@IDMaterial", DbType.Int64, oItem.IDMaterial);

          using (IDataReader oR = db.ExecuteReader(cmd))
          {
              lista = MakeUniqueDatosProvedorXMAterial(oR);
          }

      }
      catch (Exception ex)
      {
          throw new Exception("ProveedorDA.ListarProveedoresXMaterial()", ex);
      }
      finally
      {
      }
      return lista;
  }

  private IList<ProveedorBE> MakeUniqueDatosProvedorXMAterial(IDataReader oR)
  {
      IList<ProveedorBE> list = new List<ProveedorBE>();

      while (oR.Read())
          list.Add(MakeDatosMapeadosProvedorXMAterial(oR));

      return list;
  }

  private ProveedorBE MakeDatosMapeadosProvedorXMAterial(IDataReader oR)
  {
      var itemResult = new ProveedorBE();

      itemResult.IDProveedor = oR["IDProveedor"].ToInt64();
      itemResult.RucProv = oR["RucProv"].ToInt64();
      itemResult.RazonSProv = oR["RazonSProv"].ToString();
      itemResult.NroCeluProvee = oR["NroCeluProvee"].ToString();
      itemResult.NroTelfProvee = oR["NroTelfProvee"].ToString();
      return itemResult;
  }

  #endregion


 }
}
